package com.sailing.xjpb.orm.utils;

import com.google.common.base.CaseFormat;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.data.domain.Sort;

import javax.persistence.Query;
import java.io.StringReader;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * 与sql有关的工具类
 *
 * @author YaoWei
 * createDate: 2020/12/18 13:24
 */
@Slf4j
public final class SqlUtils {

    private SqlUtils() {

    }

    /**
     * sql count语句模板
     */
    private static final String COUNT_SQL_TEMPLATE = "select count(1) count from dual";
    /**
     * sql注入参数前缀
     */
    private static final String PARAMETER_KEY_PREFIX = ":";

    private static final String REG = "(\\b" +
            "(select|update|union|and|or|delete|insert|trancate|char|substr|ascii|declare|exec|count|master|into|drop" +
            "|execute)\\b)";
    private static final Pattern sqlPattern = Pattern.compile(REG, Pattern.CASE_INSENSITIVE);

    /**
     * 设置query的注入参数信息
     *
     * @param query  查询对象
     * @param params 注入参数
     */
    public static void setParameter(Query query, List<Object> params) {
        if (params != null) {
            for (int i = 0; i < params.size(); i++) {
                query.setParameter(i + 1, params.get(i));
            }
        }
    }

    /**
     * 得到注入参数key
     *
     * @param key 参数名称
     */
    public static String getParameterKey(String key) {
        return PARAMETER_KEY_PREFIX + key;
    }

    /**
     * 设置query的注入参数信息
     *
     * @param query  查询对象
     * @param params 注入参数
     */
    public static void setParameter(Query query, Map<String, Object> params) {
        if (params != null) {
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                query.setParameter(entry.getKey(), entry.getValue());
            }
        }
    }

    /**
     * 构造sql条件 in中的(?,?,?)
     *
     * @param size 问号个数
     * @return (?, ?, ?)
     */
    public static String doGetSqlIn(int size) {
        String[] a = new String[size];
        Arrays.fill(a, "?");
        return Arrays.stream(a).collect(Collectors.joining(",", "(", ")"));
    }

    /**
     * 构造sql条件in中的('value1','value2','value3')
     *
     * @param inValues in条件值列表
     * @return (' value1 ', ' value2 ', ' value3 ')
     */
    public static String doGetSqlIn(Stream<String> inValues) {
        return inValues.collect(Collectors.joining("','", "('", "')"));
    }

    /**
     * 构造sql条件in中的 ('value1','value2','value3')
     *
     * @param inValues in条件值列表
     * @return (' value1 ', ' value2 ', ' value3 ')
     */
    public static String doGetSqlIn(List<String> inValues) {
        return inValues.stream().collect(Collectors.joining("','", "('", "')"));
    }

    /**
     * 构造sql条件in中的 (:param1,:param2,:param3)和注入参数（param1，param2，param3）
     *
     * @param paramKey 注入参数key
     * @param inValues in条件值
     * @return (: param1, : param2, : param3)和注入参数（param1，param2，param3）
     */
    public static Pair<String, Map<String, Object>> getSqlArrayIn(String paramKey, Collection<?> inValues) {
        if (CollectionUtils.isEmpty(inValues)) {
            return Pair.of("any(array['']) ", new HashMap<>(1));
        }
        Pair<String, Map<String, Object>> sqlIn = getSqlIn(paramKey, inValues);
        String sqlArrayIn = sqlIn.getLeft();
        sqlArrayIn = sqlArrayIn.replace("(", "any(array[").replace(")", "])");
        return Pair.of(sqlArrayIn, sqlIn.getRight());
    }

    /**
     * 构造sql条件in中的 (:param1,:param2,:param3)和注入参数（param1，param2，param3）
     *
     * @param paramKey 注入参数key
     * @param inValues in条件值
     * @return (: param1, : param2, : param3)和注入参数（param1，param2，param3）
     */
    public static Pair<String, Map<String, Object>> getSqlIn(String paramKey, Collection<?> inValues) {
        return doGetSqlIn(paramKey, inValues, (sqlIn, params) -> {
        });
    }

    /**
     * 构造sql条件in中的 (:param1,:param2,:param3)和注入参数（param1，param2，param3）
     *
     * @param paramKey 注入参数key
     * @param inValues in条件值
     * @param consumer 查询条件和注入参数的消费函数
     */
    public static void getSqlIn(String paramKey, Collection<?> inValues, BiConsumer<CharSequence,
            Map<String, Object>> consumer) {
        doGetSqlIn(paramKey, inValues, consumer);
    }

    /**
     * 构造sql条件in中的 (:param1,:param2,:param3)和注入参数（param1，param2，param3）
     *
     * @param paramKey 注入参数key
     * @param inValues in条件值
     * @return (: param1, : param2, : param3)和注入参数（param1，param2，param3）
     */
    private static Pair<String, Map<String, Object>> doGetSqlIn(String paramKey, Collection<?> inValues,
                                                                BiConsumer<CharSequence, Map<String, Object>> consumer) {
        List<Object> paramValues = new ArrayList<>(inValues);
        List<String> parameterKeys = new ArrayList<>(inValues.size());
        Map<String, Object> params = new HashMap<>(inValues.size() + 1);
        for (int i = 0; i < paramValues.size(); i++) {
            String key = paramKey + i;
            String parameterKey = getParameterKey(key);
            parameterKeys.add(parameterKey);
            params.put(key, paramValues.get(i));
        }
        String sqlIn = parameterKeys.stream().collect(Collectors.joining(",", "(", ")"));
        consumer.accept(sqlIn, params);
        return Pair.of(sqlIn, params);
    }

    /**
     * 将select * from tableName 替换为 select count(1) total from tableName
     * 如果sql中包含group by语句则替换为 select count(1) total from (select * from tableName)
     *
     * @param querySql sql查询语句，如：select * from tableName
     */
    public static String getCountSql(String querySql) {
        try {
            Select select = getSelect(querySql);
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            // 移除order by
            plainSelect.setOrderByElements(null);
            plainSelect.getSelectItems();
            // 带groupBy和select distinct xx不能替换为select count(1)
            boolean replaceSelectBody = plainSelect.getGroupBy() == null && plainSelect.getDistinct() == null;
            if (replaceSelectBody) {
                // 将select * from tableName 替换为 select count(1) total from tableName
                List<SelectItem> countSqlSelectItems = getCountSqlSelectItems();
                plainSelect.setSelectItems(countSqlSelectItems);
                return select.toString();
            } else {
                return getSimpleCountSql(select.toString());
            }
        } catch (JSQLParserException e) {
            log.warn("sql解析出错，将使用简单的sql count-->origin sql:{}", querySql);
            log.warn("sql parse is error:{}", e.getMessage());
            return getSimpleCountSql(querySql);
        }
    }

    /**
     * 使用Sort定义的排序替换sql中原有的Sql排序
     *
     * @param querySql 查询语句
     * @param sort     排序信息
     */
    public static String getQuerySql(String querySql, Sort sort) {
        if (sort.isUnsorted()) {
            return querySql;
        }
        List<OrderByElement> orderByElements = getOrderByElements(sort);
        try {
            Select querySqlSelect = getSelect(querySql);
            PlainSelect plainSelect = (PlainSelect) querySqlSelect.getSelectBody();
            plainSelect.setOrderByElements(orderByElements);
            return querySqlSelect.toString();
        } catch (JSQLParserException e) {
            log.warn("移除排序字段出错-sql解析出错：{}", e.getMessage());
            return joinOrderBy(querySql, orderByElements);
        }
    }

    /**
     * 将查询SQL解析为SelectBody对象
     *
     * @param querySql SQL查询语句
     * @throws JSQLParserException sql解析异常
     */
    private static Select getSelect(String querySql) throws JSQLParserException {
        CCJSqlParserManager pm = new CCJSqlParserManager();
        return (Select) pm.parse(new StringReader(querySql));
    }

    /**
     * 获取group by的count语句
     *
     * @param querySql 查询语句
     */
    private static String getSimpleCountSql(String querySql) {
        return "select count(1) total from (" + querySql + ") t";
    }

    /**
     * 获取count语句查询元素字段
     *
     * @return count(1) count
     * @throws JSQLParserException sql解析异常
     */
    private static List<SelectItem> getCountSqlSelectItems() throws JSQLParserException {
        Select select = getSelect(COUNT_SQL_TEMPLATE);
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        return plainSelect.getSelectItems();
    }

    /**
     * 解析排序字段
     *
     * @param sort 排序信息
     */
    private static List<OrderByElement> getOrderByElements(Sort sort) {
        List<OrderByElement> orderByElements = new ArrayList<>();
        sort.forEach(s -> {
            OrderByElement orderByElement = new OrderByElement();
            if (!sqlPatternCheck(s.getProperty())) {
                // 排序字段
                String columnName = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, s.getProperty());
                orderByElement.setExpression(new Column(columnName));
                // 排序
                Sort.Direction direction = s.getDirection();
                orderByElement.setAsc(direction.isAscending());
                // add by YaoWei 增加对null排序支持
                Sort.NullHandling nullHandling = s.getNullHandling();
                if (nullHandling == Sort.NullHandling.NULLS_FIRST) {
                    orderByElement.setNullOrdering(OrderByElement.NullOrdering.NULLS_FIRST);
                } else if (nullHandling == Sort.NullHandling.NULLS_LAST) {
                    orderByElement.setNullOrdering(OrderByElement.NullOrdering.NULLS_LAST);
                }
                orderByElements.add(orderByElement);
            }
        });
        return orderByElements;
    }

    /**
     * 将Sql语句拼接排序语句
     *
     * @param querySql        查询语句
     * @param orderByElements 排序元素
     * @return 添加order by 后的语句
     */
    private static String joinOrderBy(String querySql, List<OrderByElement> orderByElements) {
        return querySql + " order by " + StringUtils.join(orderByElements, ",");
    }


    public static boolean sqlPatternCheck(String str) {
        return sqlPattern.matcher(str).find();
    }

}
